package com.situ.system.employee.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.situ.system.employee.model.EmployeeModel;
import com.situ.system.util.JDBCUtil;
import com.situ.system.util.MD5;

public class EmployeeDao {

	private Connection connection = null;
	private PreparedStatement ps = null;

	// 增加
	public String insert(EmployeeModel model) {
		String sql = "insert into employee(e_id, e_password, e_name, e_sex, e_date, d_id) value(?,?,?,?,?,?)";
		String pass = MD5.encode(model.getPassword());
		return "" + JDBCUtil.shuai(sql, model.getId(), pass, model.getName(), model.getSex(), model.getDate(),
				model.getD_id());
	}

	// 删除
	public String delete(EmployeeModel model) {
		String sql = "delete from employee where  e_id = ?";
		return "" + JDBCUtil.shuai(sql, model.getId());
	}

	// 修改
	public String update(EmployeeModel model) {
		String sql = "update employee set e_password = ?, e_name = ? where e_id = ?";
		return "" + JDBCUtil.shuai(sql, model.getPassword(), model.getName(), model.getId());
	}

	// 修改密码
	public String updatepass(EmployeeModel model) {
		String sql = "update employee set e_password = ? where e_id = ?";
		String pass = MD5.encode(model.getPassword());
		return "" + JDBCUtil.shuai(sql, pass, model.getId());
	}

	// 修改部门
	public String updDept(EmployeeModel model) {
		String sql = "update employee set d_id = ? where e_id = ?";
		return "" + JDBCUtil.shuai(sql, model.getD_id(), model.getId());
	}

	// 查询
	public List<EmployeeModel> select(EmployeeModel model) {
		StringBuffer sql = new StringBuffer(
				"select e_id,e_password,e_name,d_id,(select d_name from department where department.d_id=employee.d_id)d_name from employee where 1=1");
		List<Object> list = new ArrayList<Object>();
		list = where(sql, model);
		List<EmployeeModel> list2 = new ArrayList<EmployeeModel>();
		ResultSet set = null;
		try {
			connection = JDBCUtil.getConnection();
			ps = connection.prepareStatement(sql.toString());
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
			}
			set = ps.executeQuery();
			while (set.next()) {
				EmployeeModel model1 = new EmployeeModel();
				model1.setId(set.getString("e_id"));
				model1.setPassword(set.getString("e_password"));
				model1.setName(set.getString("e_name"));
				model1.setD_id(set.getString("d_id"));
				model1.setD_name(set.getString("d_name"));
				list2.add(model1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, ps);
		}
		return list2;
	}

	public Integer selectCount(EmployeeModel model) {
		StringBuffer sql = new StringBuffer("select count(1) from employee");
		ResultSet rs = null;
		try {
			connection = JDBCUtil.getConnection();
			ps = connection.prepareStatement(sql.toString());
			rs = ps.executeQuery();
			if (rs.next()) {
				System.out.println(rs.getInt(1));
				return rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, ps);
		}
		return null;
	}

	public List<Object> where(StringBuffer sql, EmployeeModel model) {
		String s_id = model.getId();
		List<Object> list = new ArrayList<Object>();
		if (s_id != null && !s_id.trim().isEmpty()) {
			sql.append(" and e_id like ?");
			list.add(s_id);
		}
		String name = model.getName();
		if (name != null && !name.trim().isEmpty()) {
			sql.append(" and e_name like ?");
			list.add(name);
		}
		if (model.isPageOn()) {
			sql.append(" limit ?,? ");
			list.add(model.getRowStart());
			list.add(model.getPageLimit());
		}
		return list;
	}
}
